<?php

class GeolocateShell extends AppShell {

    public $uses = array('Subsidio');
    public $limit = false;

    public function main() {
        $sql = "SELECT id, direccion FROM sub_subsidios AS Subsidio WHERE coordenadas IS NULL AND estado_geo = 'Sin geolocalizar' ORDER BY id ASC LIMIT 200";
        $query = $this->Subsidio->Query($sql);

        if (empty($query)) {
            $this->out("NO HAY SUBSIDIOS PARA GEOLOCALIZAR A LAS " . date("H:i:s") . " DEL " . date("d/m/Y"));
            return;
        }

        foreach ($query as $subsidio) {
            $update = "UPDATE sub_subsidios SET estado_geo = 'Geolocalizando' WHERE id=" . $subsidio['Subsidio']['id'];
            $this->Subsidio->Query($update);
        }

        foreach ($query as $subsidio) {
            // Si el flag indica que se cumplio el limite los pongo uno por uno devuelta como "Sin geolocalizar"
            if ($this->limit) {
                $update = "UPDATE sub_subsidios SET estado_geo = 'Sin geolocalizar' WHERE id=" . $subsidio['Subsidio']['id'];
                $this->Subsidio->Query($update);
                continue;
            }
                        
            // Geolocalizo
            $url_google = "http://maps.googleapis.com/maps/api/geocode/json?address=" . urlencode(trim($subsidio['Subsidio']['direccion']) . ", Ciudad de Buenos Aires, Argentina") . "&sensor=false";
            $json = file_get_contents($url_google);
            $jdata = json_decode($json, true);

            // Si google nos dice que se cumplio el limite lo pongo devuelta como "Sin geolocalizar" y cambio el flag
            if ($jdata['status'] == "OVER_QUERY_LIMIT") {
                $update = "UPDATE sub_subsidios SET estado_geo = 'Sin geolocalizar' WHERE id=" . $subsidio['Subsidio']['id'];
                $this->Subsidio->Query($update);
                $this->out("OVER_QUERY_LIMIT");
                $this->out("---------------------------------------------------------------");
                $this->limit = true;
                continue;
            }

            $array = array();
            
            if ($jdata['status'] == "OK" && count($jdata['results'])) {
                foreach ($jdata['results'][0]['address_components'] as $comp) {
                    if ($comp['types'][0] == 'street_number') {
                        $array['altura'] = $comp['long_name'];
                    } elseif ($comp['types'][0] == 'route') {
                        $array['calle'] = $comp['long_name'];
                    } elseif ($comp['types'][0] == 'neighborhood') {
                        $array['barrio'] = $comp['long_name'];
                    }
                }

                $array['coordenadas'] = $jdata['results'][0]['geometry']['location']['lat'] . "," . $jdata['results'][0]['geometry']['location']['lng'];

                if (isset($array['calle']) && isset($array['altura']) && isset($array['barrio']) && isset($array['coordenadas'])) {
                    $last_update = "UPDATE sub_subsidios ";
                    $last_update.= "SET calle = '" . mysql_escape_string($array['calle']) . "', ";
                    $last_update.= "altura = " . $array['altura'] . ", ";
                    $last_update.= "barrio = '" . mysql_escape_string($array['barrio']) . "', ";
                    $last_update.= "coordenadas = '" . mysql_escape_string($array['coordenadas']) . "', ";
                    $last_update.= "estado_geo = 'Geolocalizado' ";
                    $last_update.= "WHERE id=" . $subsidio['Subsidio']['id'];
                } else {
                    $last_update = "UPDATE sub_subsidios SET estado_geo = 'No geolocalizable' WHERE id=" . $subsidio['Subsidio']['id'];
                }
                $this->Subsidio->Query($last_update);
                //$this->logQuery($last_update);
            }
        }


        $this->out("FIN");
        $this->out("---------------------------------------------------------------");
    }

    private function logQuery($query) {
        if (substr($query, -1) != ';') {
            $query.= ';';
        }
        $file = ROOT . '/coordenadas.sql';
        $current = file_get_contents($file);
        $current .= $query . "\n";
        file_put_contents($file, $current);
    }

}